# ---------------------- 模拟读取student.xlsx数据（无需安装库） ----------------------
# 将Excel中的45条学生数据转为Python列表，每条数据对应一个字典
student_data = [
    {"序号":1,"学号":"2123310401","姓名":"陈福强","性别":"男","年龄":18,"身份证号":"469***************8918","电话号码":"152*****0858","家庭地址":"海南省儋州市国营红岭农场桥南路11号"},
    {"序号":2,"学号":"2123310402","姓名":"陈汉宇","性别":"女","年龄":19,"身份证号":"460***************6022","电话号码":"188*****1295","家庭地址":"海南省白沙县金波乡金波十八队"},
    {"序号":3,"学号":"2123310403","姓名":"陈南成","性别":"男","年龄":18,"身份证号":"460***************6835","电话号码":"188*****2861","家庭地址":"海南省临高县东英镇东英居委会解放路南62号"},
    {"序号":4,"学号":"2123310404","姓名":"陈鹏","性别":"男","年龄":19,"身份证号":"220***************1617","电话号码":"182*****6463","家庭地址":"吉林省松原市长岭县大兴镇大兴村"},
    {"序号":5,"学号":"2123310405","姓名":"陈松杰","性别":"男","年龄":20,"身份证号":"460***************0917","电话号码":"188*****4928","家庭地址":"海南省海口市美兰区海府路101-3号"},
    {"序号":6,"学号":"2123310406","姓名":"陈宗辉","性别":"男","年龄":16,"身份证号":"460***************001X","电话号码":"175*****7120","家庭地址":"海南省儋州市那大镇学府美院803号"},
    {"序号":7,"学号":"2123310407","姓名":"方贵涛","性别":"男","年龄":29,"身份证号":"460***************1211","电话号码":"177*****0812","家庭地址":"海南省三亚市吉阳区市仔村南新悦城小区"},
    {"序号":8,"学号":"2123310408","姓名":"符大豪","性别":"男","年龄":31,"身份证号":"460***************0714","电话号码":"183*****2960","家庭地址":"海南省文昌市重兴镇东风村委会高步一村"},
    {"序号":9,"学号":"2123310409","姓名":"符日恩","性别":"男","年龄":22,"身份证号":"469***************4971","电话号码":"139*****2262","家庭地址":"海南省东方市八所镇大占坡村"},
    {"序号":10,"学号":"2123310410","姓名":"符显强","性别":"男","年龄":34,"身份证号":"460***************0513","电话号码":"155*****7431","家庭地址":"海南省文昌市拥军路14号"},
    {"序号":11,"学号":"2123310411","姓名":"何影影","性别":"女","年龄":27,"身份证号":"460***************6826","电话号码":"132*****2698","家庭地址":"海南省东方市东河镇金炳村"},
    {"序号":12,"学号":"2123310412","姓名":"黄馨丹","性别":"女","年龄":36,"身份证号":"460***************0425","电话号码":"185*****6173","家庭地址":"海南省保亭黎族苗族自治县什玲镇椰村村委会平土村"},
    {"序号":13,"学号":"2123310413","姓名":"黄耀","性别":"男","年龄":17,"身份证号":"460***************5916","电话号码":"187*****5690","家庭地址":"海南省海口市琼山区龙昆南路59号"},
    {"序号":14,"学号":"2123310414","姓名":"黄业鑫","性别":"男","年龄":20,"身份证号":"460***************0038","电话号码":"181*****1676","家庭地址":"海南省东方市八所镇铁路南工人新村3栋102号"},
    {"序号":15,"学号":"2123310415","姓名":"纪新帆","性别":"男","年龄":30,"身份证号":"460***************0610","电话号码":"139*****6426","家庭地址":"海南省海口市秀英街道金鼎路金城苑"},
    {"序号":16,"学号":"2123310416","姓名":"黎俊文","性别":"男","年龄":32,"身份证号":"460***************4433","电话号码":"131*****4250","家庭地址":"海南省三亚市崖州区崖城镇中心大道"},
    {"序号":17,"学号":"2123310417","姓名":"梁敏敏","性别":"女","年龄":23,"身份证号":"450***************2521","电话号码":"173*****2489","家庭地址":"广西梧州市苍梧县岭脚镇龙潭村"},
    {"序号":18,"学号":"2123310418","姓名":"林铭","性别":"男","年龄":27,"身份证号":"460***************1516","电话号码":"158*****7607","家庭地址":"海南省海口市海甸岛二东路广益新村"},
    {"序号":19,"学号":"2123310419","姓名":"林士师","性别":"男","年龄":31,"身份证号":"460***************5214","电话号码":"189*****2873","家庭地址":"海南省海口市滨江街道高登东街110号琼山地税局九层楼宿舍"},
    {"序号":20,"学号":"2123310420","姓名":"刘政见","性别":"男","年龄":19,"身份证号":"131***************241X","电话号码":"152*****8629","家庭地址":"河北省廊坊市广阳区大枣林庄村"},
    {"序号":21,"学号":"2123310421","姓名":"龙仕锦","性别":"男","年龄":15,"身份证号":"460***************581X","电话号码":"186*****2003","家庭地址":"海南省儋州市八一总场文明东路十二栋六号"},
    {"序号":22,"学号":"2123310422","姓名":"潘金娜","性别":"女","年龄":37,"身份证号":"460***************3723","电话号码":"173*****4081","家庭地址":"海南省文昌市公坡镇锦东村委会东排山村6号"},
    {"序号":23,"学号":"2123310423","姓名":"彭恩慧","性别":"男","年龄":32,"身份证号":"460***************0018","电话号码":"130*****5806","家庭地址":"海南省儋州市兰洋镇番加农场"},
    {"序号":24,"学号":"2123310424","姓名":"阮金海","性别":"男","年龄":28,"身份证号":"440***************6153","电话号码":"188*****7938","家庭地址":"海南省海口市琼山区新大洲大道222号"},
    {"序号":25,"学号":"2123310425","姓名":"汤健","性别":"男","年龄":33,"身份证号":"533***************0011","电话号码":"135*****3225","家庭地址":"云南省丽江市古城区香江花园一区35栋501"},
    {"序号":26,"学号":"2123310426","姓名":"王炳坤","性别":"男","年龄":42,"身份证号":"460***************0896","电话号码":"133*****8259","家庭地址":"海南省东方市八所镇皇宁村二区七巷一号"},
    {"序号":27,"学号":"2123310427","姓名":"王经助","性别":"男","年龄":71,"身份证号":"469***************3870","电话号码":"151*****0837","家庭地址":"海南省乐东县黎族自治县佛罗镇白井村委会永德村一队60号"},
    {"序号":28,"学号":"2123310428","姓名":"王康荣","性别":"男","年龄":83,"身份证号":"460***************5839","电话号码":"151*****4329","家庭地址":"海南省海口市琼山区三门坡镇北冲村"},
    {"序号":29,"学号":"2123310429","姓名":"王著","性别":"男","年龄":45,"身份证号":"460***************0014","电话号码":"153*****6805","家庭地址":"海南省海口市府城街道海怡路21号"},
    {"序号":30,"学号":"2123310430","姓名":"王姿","性别":"女","年龄":34,"身份证号":"460***************2623","电话号码":"139*****2760","家庭地址":"海南省海口市琼山区新桥路"},
    {"序号":31,"学号":"2123310431","姓名":"韦佳壮","性别":"男","年龄":43,"身份证号":"450***************1231","电话号码":"150*****8045","家庭地址":"广西壮族自治区港北区庆丰镇万新村万安屯255号"},
    {"序号":32,"学号":"2123310432","姓名":"翁冉","性别":"女","年龄":32,"身份证号":"411***************6320","电话号码":"180*****2463","家庭地址":"河南省郑州市新郑市孟庄镇唐河村"},
    {"序号":33,"学号":"2123310433","姓名":"谢金豆","性别":"男","年龄":56,"身份证号":"511***************0971","电话号码":"147*****0695","家庭地址":"四川省广安市邻水县观音桥镇七一村5组38号"},
    {"序号":34,"学号":"2123310434","姓名":"徐文杰","性别":"男","年龄":29,"身份证号":"430***************0537","电话号码":"155*****7886","家庭地址":"湖南省邵阳市武冈市商三街十栋"},
    {"序号":35,"学号":"2123310435","姓名":"徐鑫诚","性别":"男","年龄":31,"身份证号":"441***************7113","电话号码":"134*****1513","家庭地址":"广东省惠州市淡水镇星河丹堤"},
    {"序号":36,"学号":"2123310436","姓名":"颜璟钧","性别":"男","年龄":15,"身份证号":"460***************0013","电话号码":"182*****6608","家庭地址":"海南省临高县临城镇跃进居委会跃进路生产资料仓库宿舍4栋110号"},
    {"序号":37,"学号":"2123310437","姓名":"杨家意","性别":"男","年龄":19,"身份证号":"450***************1219","电话号码":"173*****1413","家庭地址":"广西省贵港市港北区庆丰镇联塘村金塘屯247号"},
    {"序号":38,"学号":"2123310438","姓名":"杨杰","性别":"男","年龄":26,"身份证号":"522***************0819","电话号码":"175*****3937","家庭地址":"贵州省铜仁市石阡县龙塘镇关门岩村三组"},
    {"序号":39,"学号":"2123310439","姓名":"杨锦","性别":"男","年龄":37,"身份证号":"441***************0411","电话号码":"137*****0423","家庭地址":"广东省梅州市丰顺县丰良镇"},
    {"序号":40,"学号":"2123310440","姓名":"张昌子","性别":"男","年龄":41,"身份证号":"460***************2219","电话号码":"138*****9062","家庭地址":"海南省海口市琼山区滨江路河口路"},
    {"序号":41,"学号":"2123310441","姓名":"张福胜","性别":"男","年龄":52,"身份证号":"460***************7470","电话号码":"176*****7681","家庭地址":"海南省乐东黎族自治县志仲镇塔丰村委会塔丰七队69号"},
    {"序号":42,"学号":"2123310442","姓名":"张祥","性别":"男","年龄":43,"身份证号":"460***************6411","电话号码":"151*****4460","家庭地址":"海南省昌江黎族自治县海尾镇海楼街四巷28号"},
    {"序号":43,"学号":"2123310443","姓名":"张运旺","性别":"男","年龄":36,"身份证号":"469***************4614","电话号码":"155*****1585","家庭地址":"海南省琼海市大路镇礼合3村"},
    {"序号":44,"学号":"2123310444","姓名":"郑孟陵","性别":"女","年龄":43,"身份证号":"469***************4427","电话号码":"182*****1330","家庭地址":"海南省琼海市龙江镇"},
    {"序号":45,"学号":"2123310445","姓名":"郑有环","性别":"男","年龄":25,"身份证号":"469***************4415","电话号码":"199*****6503","家庭地址":"海南省陵水县群英乡祖空村委会针内村12号"}
]


# ---------------------- 1. 打印输出student.xlsx文件内容 ----------------------
print("="*120)
print("任务1：student.xlsx文件内容（共45条数据）")
print("="*120)
# 打印表头
headers = ["序号", "学号", "姓名", "性别", "年龄", "身份证号", "电话号码", "家庭地址"]
print(f"{headers[0]:<4}{headers[1]:<12}{headers[2]:<6}{headers[3]:<4}{headers[4]:<4}{headers[5]:<20}{headers[6]:<12}{headers[7]}")
print("-"*120)
# 打印每条学生数据
for student in student_data:
    print(f"{student['序号']:<4}{student['学号']:<12}{student['姓名']:<6}{student['性别']:<4}{student['年龄']:<4}{student['身份证号']:<20}{student['电话号码']:<12}{student['家庭地址']}")
print("\n")


# ---------------------- 2. 按性别统计学生人数 ----------------------
gender_count = {"男": 0, "女": 0}
for student in student_data:
    gender = student["性别"]
    if gender == "男":
        gender_count["男"] += 1
    else:
        gender_count["女"] += 1

print("="*50)
print("任务2：按性别统计学生人数")
print("="*50)
print(f"男生：{gender_count['男']}人")
print(f"女生：{gender_count['女']}人")
print(f"总人数：{gender_count['男'] + gender_count['女']}人")
print("\n")


# ---------------------- 3. 按年龄段统计学生人数 ----------------------
# 定义年龄段划分函数
def get_age_group(age):
    if 15 <= age <= 18:
        return "15-18岁（未成年/低龄）"
    elif 19 <= age <= 25:
        return "19-25岁（青年）"
    elif 26 <= age <= 35:
        return "26-35岁（中青年）"
    elif 36 <= age <= 50:
        return "36-50岁（中年）"
    else:
        return "50岁以上（高龄）"

# 统计各年龄段人数
age_group_count = {}
for student in student_data:
    age = student["年龄"]
    group = get_age_group(age)
    if group in age_group_count:
        age_group_count[group] += 1
    else:
        age_group_count[group] = 1

# 按年龄段顺序排序（确保输出逻辑清晰）
sorted_age_groups = [
    "15-18岁（未成年/低龄）",
    "19-25岁（青年）",
    "26-35岁（中青年）",
    "36-50岁（中年）",
    "50岁以上（高龄）"
]

print("="*50)
print("任务3：按年龄段统计学生人数")
print("="*50)
total_age = 0
for group in sorted_age_groups:
    count = age_group_count.get(group, 0)
    print(f"{group}：{count}人")
    total_age += count
print(f"总人数：{total_age}人")
print("\n")


# ---------------------- 4. 按地区统计学生人数 ----------------------
# 定义地区提取函数（海南省按市县，外省按省份）
def extract_region(address):
    # 处理海南省地址（提取市县）
    if "海南省" in address:
        hainan_cities = ["海口市", "儋州市", "白沙县", "临高县", "东方市", "文昌市", 
                        "保亭黎族苗族自治县", "乐东县黎族自治县", "昌江黎族自治县", 
                        "琼海市", "陵水县", "三亚市"]
        for city in hainan_cities:
            if city in address:
                return f"海南省-{city}"
        return "海南省-其他市县"
    # 处理外省地址（提取省份/自治区）
    else:
        # 覆盖所有外省地址的省份关键词
        province_keywords = {
            "吉林省": "吉林省",
            "广西": "广西壮族自治区",  # 匹配“广西梧州市”“广西省贵港市”
            "河北": "河北省",
            "河南": "河南省",
            "四川": "四川省",
            "湖南": "湖南省",
            "广东": "广东省",
            "贵州": "贵州省",
            "云南": "云南省"
        }
        for keyword, province in province_keywords.items():
            if keyword in address:
                return province
        return "其他省份"

# 统计各地区人数
region_count = {}
for student in student_data:
    address = student["家庭地址"]
    region = extract_region(address)
    if region in region_count:
        region_count[region] += 1
    else:
        region_count[region] = 1

print("="*50)
print("任务4：按地区统计学生人数")
print("="*50)
total_region = 0
# 先打印海南省各市县，再打印外省（逻辑更清晰）
hainan_regions = [r for r in region_count.keys() if r.startswith("海南省-")]
other_regions = [r for r in region_count.keys() if not r.startswith("海南省-")]

for region in hainan_regions:
    count = region_count[region]
    print(f"{region}：{count}人")
    total_region += count
for region in other_regions:
    count = region_count[region]
    print(f"{region}：{count}人")
    total_region += count
print(f"总人数：{total_region}人")